# -*- coding: utf-8 -*-

from flask import request, render_template, jsonify, flash, url_for, session, redirect, make_response

from flask.ext.openid import OpenID
from flask.ext.wtf import Form
from flask_wtf.csrf import CsrfProtect
from flask.ext.login import login_user, logout_user, login_required, current_user

from wtforms import StringField, PasswordField, IntegerField, BooleanField, SubmitField, RadioField, SelectMultipleField
from wtforms.validators import Required, Length, Email

import datetime, calendar

from flask.ext import excel
import pyexcel.ext.xls

import os, json, sys, re,random

from functools import wraps

from app import app, db, login_manager, ccLog

from .models import Teacher, Student, HistoryRecord, Schedule, SubsContent, SubsTime,Article,Slider,Notes
from .forms import LoginForm, VerifyForm, StuCommitForm

from .utilities import DateAndTimeHandler, SmsHandler, NjuUtility 

from config import ALLOWED_EXTENSIONS
from config_user import ROLE_ADMIN,ROLE_TEACHER,DISTRICT_GULOU,DISTRICT_XIANLIN
from config_user import CONSULT_TODO, CONSULT_COMPLETE, CONSULT_CANCEL, INVALID_TAG
from config_user import STU_NORMAL, STU_BLACK, STU_INIT, STU_ARRANGED, COOKIE_VALID_PERIOD, ALLOWED_IMAGE_EXT

import multiprocessing
from multiprocessing import Pool

CANCEL_TO_BLACK=3

def SendSmsMsg3(url):    
    #print(url)
    urlutf8 = None
    try:
        urlutf8 = unicode(url).encode("utf-8")
    except:
        urlutf8 = url
    #print(url1)
    try:
        import urllib.request
        #import sys
        #reload(sys)
        #sys.setdefaultencoding('UTF-8')
        request = urllib.request.urlopen(urlutf8)
        return request.read()
    except:
        try:
            import urllib
            request = urllib.urlopen(urlutf8)
            return request.read()
        except Exception as e:
            #print(e)
            pass
        
    return ""

#辅助及回调函数区
def __OutDebugInfo(a,*b):
    return
    sys.stdout.write(a)
    for x in b:
        sys.stdout.write(x)
        
    print('')
    return

#载入用户回调函数   
@login_manager.user_loader
def load_user(user_id):
    return Teacher.query.get(int(user_id))

##! 自定义学生用户登录验证
def stu_login_user(session, user):
    session['stu_logged_in'] = True
    session['stu_user_id'] = user.id
    session['stu_contact'] = user.contact

def stu_logout_user(session, user):
    session.pop('stu_logged_in',None)
    session.pop('stu_user_id',None)
    session.pop('stu_contact',None)

def gen_rnd_filename():
  filename_prefix = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
  return '%s%s' % (filename_prefix, str(random.randrange(1000, 10000)))

def stu_login_required(view):
    @wraps(view)
    def wrapper(*args, **kwargs):
        if 'stu_logged_in' in session:
            return view(*args, **kwargs)
        else:
            flash('账户已超时自动退出，请重新登录！')
            return redirect(url_for('student_query_login_student',next=request.url))
    return wrapper

@app.route('/secret')
@login_required
def secret():
    return 'Only authenticated users are allowed!'
    
def GetYYinfoFrompbid(pbid):
    yyinfo = ""
    sch = Schedule.query.filter_by(id=pbid).first()
    if sch is not None:
        yyinfo = "您已选择 " + sch.tch_name + "<br>" + DateAndTimeHandler().GetYYDateAndTime(sch.wkd_id)+" "+DateAndTimeHandler().toDateSeqStr(sch.wkd_id) + " " + sch.stm_id + " 咨询预约"
        
    return yyinfo

##! 主页
@app.route('/', methods=['GET'])
@app.route('/index', methods=['GET', 'POST'])
def index():
    return render_template('index.html',form=Form())

##! 学生用户选择咨询时段后登录、登出
@app.route('/get_login_student_page', methods=['GET','POST'])
def get_login_student_page():
    tcid = request.args.get('tcid')
    pbid = request.args.get('pbid')
    if tcid is not None and pbid is not None:
        session['tcid'] = tcid
        session['pbid'] = pbid
    else:
        flash("您选择的预约咨询时和时间排班有误，请重新选择")
        return redirect(url_for('get_teacher_schedule'))
        
    if 'stu_logged_in' in session:
        stuinfo = {}
        contact = session.get('stu_contact')
        stuinfo['contact'] = contact
        stu = Student.query.filter_by(contact=contact).first()
        newcomer = True if stu.status==STU_INIT else False
        if not newcomer:
            stuinfo['name'] = "" if stu.name is None else stu.name
            stuinfo['sex'] = "" if stu.sex is None else stu.sex
            stuinfo['dept'] = "" if stu.department is None else stu.department
            stuinfo['major'] = "" if stu.major is None else stu.major
            stuinfo['grade'] = "" if stu.grade is None else stu.grade
            stuinfo['stuid'] = "" if stu.stuID is None else stu.stuID
            
        stuinfo['yyinfo'] = GetYYinfoFrompbid(pbid)
            
        return render_template('student_base_info.html',form=Form(),stuinfo=stuinfo,newcomer=newcomer)
    
    cooname = request.cookies.get('username')
    coopw = request.cookies.get('userpw')
    coorm = request.cookies.get('userrm')
    reme = None
    if cooname is None:
        cooname,coopw,coorm = "","",""
        flash("初次登录时自动创建账号")
    
    return render_template('student_login.html',form=LoginForm(),coct=cooname,copw=coopw,corm=coorm)
    
@app.route('/login_student', methods=['GET','POST'])
def login_student():
    form = LoginForm()
    
    contact = form.contact.data
    password = form.password.data 
    rememberme = form.rememberme.data
    
    stu = Student.query.filter_by(contact=contact).first()

    msg = ""
    
    cooname = request.cookies.get('username')
    coopw = request.cookies.get('userpw')
    coorm = request.cookies.get('userrm')
    if cooname is None:
        cooname,coopw,coorm = "","",""
    
    p2=re.compile('^0\d{2,3}\d{7,8}$|^1[34578]\d{9}$|^147\d{8}')
    if contact is None or p2.match(contact) is None:
        msg = "请输入正确的电话号码"
        flash(msg)
        return render_template('student_login.html',form=LoginForm(),coct=cooname,copw=coopw,corm=coorm)

    if stu is not None:
        if stu.verify_password(password):          
            pass
        else:
            msg = "您输入的密码有误"
            flash(msg)
            return render_template('student_login.html',form=LoginForm(),coct=cooname,copw=coopw,corm=coorm)
    else:
        try:
            stu = Student("name:"+str(contact), "None", contact, password, "ID:"+str(contact), "email:"+str(contact), "", "", "", 0, STU_INIT, datetime.datetime.now())
            db.session.add(stu)
            db.session.commit()
            msg = "已为您创建账号："+contact
            flash(msg)
        except Exception as e:
            flash(str(e))
            return render_template('student_login.html',form=LoginForm(),coct=cooname,copw=coopw,corm=coorm)   
        
    stu_login_user(session, stu)
    
    stuinfo = {}
    stuinfo['contact'] = contact
    newcomer = True if stu.status==STU_INIT else False
    if not newcomer:
        stuinfo['name'] = "" if stu.name is None else stu.name
        stuinfo['sex'] = "" if stu.sex is None else stu.sex
        stuinfo['dept'] = "" if stu.department is None else stu.department
        stuinfo['major'] = "" if stu.major is None else stu.major
        stuinfo['grade'] = "" if stu.grade is None else stu.grade
        stuinfo['stuid'] = "" if stu.stuID is None else stu.stuID
    
    stuinfo['yyinfo'] = GetYYinfoFrompbid(session.get('pbid'))
            
    response = make_response(render_template('student_base_info.html',form=Form(),stuinfo=stuinfo,newcomer=newcomer))  
    if rememberme is True:
        response.set_cookie('username',value=contact,max_age=COOKIE_VALID_PERIOD)
        response.set_cookie('userpw',value=stu.password,max_age=COOKIE_VALID_PERIOD)
        response.set_cookie('userrm',value="checked",max_age=COOKIE_VALID_PERIOD)
    else:
        response.set_cookie('username',value=contact,max_age=0)
        response.set_cookie('userpw',value=stu.password,max_age=0)
        response.set_cookie('userrm',value="checked",max_age=0)
      
    return response
    
@app.route('/logout_student', methods=['GET', 'POST'])
@stu_login_required
def logout_student():
    contact = session.get('stu_contact')
    stu = Student.query.filter_by(contact=contact).first()
    stu_logout_user(session, stu)
    
    cooname = request.cookies.get('username')
    coopw = request.cookies.get('userpw')
    coorm = request.cookies.get('userrm')
    if cooname is None:
        cooname,coopw,coorm = "","",""
    
    return render_template('student_login.html',form=LoginForm(),coct=cooname,copw=coopw,corm=coorm) 

@app.route('/student_query_login_student', methods=['GET','POST'])
def student_query_login_student():
    if 'stu_logged_in' in session:
        return redirect(url_for('get_query_cancel_page'))
        
    cooname = request.cookies.get('username')
    coopw = request.cookies.get('userpw')
    coorm = request.cookies.get('userrm')
    if cooname is None:
        cooname,coopw,coorm = "","",""
    return render_template('student_login_query.html',form=LoginForm(),coct=cooname,copw=coopw,corm=coorm)
    
@app.route('/get_consultant_story_page', methods=['GET','POST'])
def get_consultant_story_page():
    articles_type_1 = Article.query.filter_by(type=1).order_by(Article.id.desc()).all()
    articles_type_2 = Article.query.filter_by(type=2).order_by(Article.id.desc()).all()
    articles_type_3 = Article.query.filter_by(type=3).order_by(Article.id.desc()).all()
    sliders = Slider.query.order_by(Slider.id.desc()).all()
    return render_template('teacher_story.html',form=Form(),sliders=sliders,articles_type_1=articles_type_1,articles_type_2=articles_type_2,articles_type_3=articles_type_3)

@app.route('/directed_login_student', methods=['GET','POST'])
def directed_login_student():
    form = LoginForm()
    
    contact = form.contact.data
    password = form.password.data 
    rememberme = form.rememberme.data
    
    stu = Student.query.filter_by(contact=contact).first()

    msg = ""

    if stu is not None:
        if stu.verify_password(password):          
            pass
        else:
            msg = "您输入的密码有误"
            flash(msg)
            cooname = request.cookies.get('username')
            coopw = request.cookies.get('userpw')
            coorm = request.cookies.get('userrm')
            if cooname is None:
                cooname,coopw,coorm = "","",""
            return render_template('student_login_query.html',form=LoginForm(),coct=cooname,copw=coopw,corm=coorm)
    else:
        msg = "请先点击预约按钮完成注册"
        flash(msg)
        return redirect(url_for('get_teacher_schedule'))    
        
    stu_login_user(session, stu)
        
    response = make_response(redirect(url_for('get_query_cancel_page',next=request.url)))  
    if rememberme is True:
        response.set_cookie('username',value=contact,max_age=COOKIE_VALID_PERIOD)
        response.set_cookie('userpw',value=stu.password,max_age=COOKIE_VALID_PERIOD)
        response.set_cookie('userrm',value="checked",max_age=COOKIE_VALID_PERIOD)
    else:
        response.set_cookie('username',value=contact,max_age=0)
        response.set_cookie('userpw',value=stu.password,max_age=0)
        response.set_cookie('userrm',value="checked",max_age=0)
      
    return response  

@app.route('/directed_logout_student', methods=['GET', 'POST'])
@stu_login_required
def directed_logout_student():
    contact = session.get('stu_contact')
    stu = Student.query.filter_by(contact=contact).first()
    stu_logout_user(session, stu)
    
    return render_template('index.html',form=Form())
    
##! 学生用户登录后显示内容
@app.route('/get_login_content_page', methods=['GET','POST'])
def get_login_content_page():
    return render_template('student_center.html',form=LoginForm())

@app.route('/get_teacher_intro_detail', methods=['GET', 'POST'])
def get_teacher_intro_detail():
    id = request.args.get('id')
    if id:
        tch = Teacher.query.filter_by(seq=id).first()
        if tch is None:
            return render_template('msg_handler.html', msg="id为空")
    else:
        return render_template('msg_handler.html', msg="id为空")
    return render_template('teacher_intro_detail.html', tch=tch)

@app.route('/get_teacher_schedule', methods=['GET', 'POST'])
def get_teacher_schedule():
    tchlists = []
    gl_tchlists = []
    
    notes = Notes.query.filter_by(type="排班版面").order_by(Notes.seq).all()
    
    disdb = None #不可预约起始日期
    disde = None #不可预约终止日期
    yy_interval = None
    admin = Teacher.query.filter_by(role=ROLE_ADMIN).first()
    if admin:
        yy_interval = admin.imgname
        if yy_interval is not None and yy_interval!="":
            try:
                bl = yy_interval.split(':')[0].split('-')
                disdb = datetime.datetime(int(bl[0]),int(bl[1]),int(bl[2]))
                el = yy_interval.split(':')[1].split('-')
                disde = datetime.datetime(int(el[0]),int(el[1]),int(el[2]))
            except:
                pass
        else:
            pass
    else:
        pass
    
    teacher = Teacher.query.filter_by(role=ROLE_TEACHER).all()
    for item in teacher:
        have_unbooking = False
        tch_disdb = None
        tch_disde = None
        if item.href is not None:
            unbooking_int = item.href
            try:
                tch_bl = unbooking_int.split(':')[0].split('-')
                tch_disdb = datetime.datetime(int(tch_bl[0]),int(tch_bl[1]),int(tch_bl[2]))
                tch_el = unbooking_int.split(':')[1].split('-')
                tch_disde = datetime.datetime(int(tch_el[0]),int(tch_el[1]),int(tch_el[2]))
                have_unbooking = True
                #today = datetime.datetime.now()
                #if today.strftime("%Y-%m-%d")>=tch_disdb.strftime("%Y-%m-%d") and today.strftime("%Y-%m-%d")<=tch_disde.strftime("%Y-%m-%d"):
                #    have_unbooking = True
            except:
                tch_disdb = None
                tch_disde = None
                pass
            
        tchinfo = {}
        
        schli = []
        schs = Schedule.query.filter_by(tch_id=item.seq).all()
        for elem in schs:
            schinfo = {}
            schinfo['id'] = elem.id
            dateseq = elem.wkd_id
            timeseq = elem.stm_id
            schinfo['zxdate'] = DateAndTimeHandler().GetYYDateAndTime(dateseq)+" "+DateAndTimeHandler().toDateSeqStr(dateseq)+" "+timeseq
            
            int_dateseq = 0
            try:
                int_dateseq = int(dateseq)
            except:
                pass
            today = datetime.datetime.now().weekday()
            yyforward = True #int_dateseq>(today+1)
            nextweek = today==calendar.SATURDAY or today==calendar.SUNDAY
            canbook = False if elem.isboot else True
            
            if disdb is not None:
                yydate_ = DateAndTimeHandler().GetYYDate_(dateseq)
                if yydate_.strftime("%Y-%m-%d")>=disdb.strftime("%Y-%m-%d") and yydate_.strftime("%Y-%m-%d")<=disde.strftime("%Y-%m-%d"):
                    canbook = False
                    
            if have_unbooking and tch_disdb!=None and tch_disde!=None:
                yydate_ = DateAndTimeHandler().GetYYDate_(dateseq)
                if yydate_.strftime("%Y-%m-%d")>=tch_disdb.strftime("%Y-%m-%d") and yydate_.strftime("%Y-%m-%d")<=tch_disde.strftime("%Y-%m-%d"):
                    canbook = False
                
            schinfo['booked'] = not (canbook and (nextweek or yyforward))
            if elem.campus == DISTRICT_GULOU:
                schinfo['campus'] = "鼓楼"
            elif elem.campus == DISTRICT_XIANLIN:
                schinfo['campus'] = "仙林"
            else:
                pass
            
            schli.append(schinfo)
        
        tchinfo['id'] = item.seq
        tchinfo['name'] = item.name
        tchinfo['csitem'] = schli
        tchinfo['intro'] = item.intro if item.intro !="" else "暂无简介"
        tchinfo['image'] = "/static/tchimgs/"+(item.imgname if item.imgname is not None else "main_icon.jpg")
        
        tchlists.append(tchinfo)

    return render_template('teacher_info_campus.html', form=Form(),tchlists=tchlists,notes=notes)

#预约并注册记录学生信息
@app.route('/register_data', methods=['GET', 'POST'])
@stu_login_required
def register_data():
    __OutDebugInfo("register_data:预约并注册记录学生信息")
    contact = session.get('stu_contact')
    stu = Student.query.filter_by(contact=contact).first()
    form = StuCommitForm()
    if form.validate_on_submit():
        pass
    else:
        msg = "请将注册信息填写完整！"
        flash(msg)

        stuinfo = {}
        stuinfo['contact'] = contact

        newcomer = True if stu.status==STU_INIT else False
        if not newcomer:
            stuinfo['name'] = "" if stu.name is None else stu.name
            stuinfo['sex'] = "" if stu.sex is None else stu.sex
            stuinfo['dept'] = "" if stu.department is None else stu.department
            stuinfo['major'] = "" if stu.major is None else stu.major
            stuinfo['grade'] = "" if stu.grade is None else stu.grade
            stuinfo['stuid'] = "" if stu.stuID is None else stu.stuID
            
        stuinfo['yyinfo'] = GetYYinfoFrompbid(session.get('pbid'))
        
        return render_template('student_base_info.html',form=LoginForm(),stuinfo=stuinfo,newcomer=newcomer)

    name = form.name.data
    sex = form.sex.data
    department = form.department.data
    major = form.major.data
    grade = form.grade.data
    stuid = form.stuid.data
    
    tcid = session.get('tcid')
    pbid = session.get('pbid')
    content = form.content.data
    demands = form.demands.data
    
    #contact = form.contact.data
    verycode = form.verycode.data
    msg = ""
    
    haserror = False
    if stu.status == STU_INIT:
        vcis = session.get('very_code')
        if vcis is None:
            msg = "请先获取验证码"
            haserror = True
        elif verycode==vcis:
            stu.status = STU_NORMAL
            db.session.add(stu)
            db.session.commit()
        else:
            msg = "验证码错误，请重新输入"
            haserror = True
            
    if pbid is None:
        msg="您要提交的预约信息已经过期，请重新在两分钟内完成提交"
        haserror = True
    
    if haserror:
        flash(msg)
        stuinfo = {}
        stuinfo['contact'] = contact
        newcomer = True if stu.status==STU_INIT else False
        stuinfo['name'] = name
        stuinfo['sex'] = sex
        stuinfo['dept'] = department
        stuinfo['major'] = major
        stuinfo['grade'] = grade
        stuinfo['stuid'] = stuid
           
        stuinfo['yyinfo'] = GetYYinfoFrompbid(session.get('pbid'))
        return render_template('student_base_info.html',form=LoginForm(),stuinfo=stuinfo,newcomer=newcomer)
    
    exist = Student.query.filter_by(contact=contact).first()
    if exist is None:
        msg = "发生未知错误，用户不存在"
        return render_template('msg_handler.html',msg=msg)
    else:               
        if exist.currecid != INVALID_TAG:
            msg = "您已存在预约信息，请在完成咨询之后再进行新的预约！"
            flash(msg)
            return redirect(url_for('get_query_cancel_page'))
            #return render_template('student_center.html',form=LoginForm())
        elif exist.status == STU_BLACK:
            msg = "您已被限制预约权限，请致电就业中心89687519，83597519，联系管理员处理！"
            return render_template('msg_handler.html',msg=msg)

        exist.name = name
        exist.sex = sex
        exist.department = department
        exist.major = major
        exist.grade = grade
        exist.stuID = stuid

    sch = Schedule.query.filter_by(id=pbid).first()
    if sch is not None and sch.isboot==0:
        sch.isboot = 1
        exist.status = STU_NORMAL
        
        yytime = DateAndTimeHandler().GetYYDateAndTime(sch.wkd_id)
        yytime = yytime+'-'+sch.stm_id
        strcont = u""
        for item in content:
            subcont = SubsContent.query.filter_by(id=int(item)).first()
            if subcont is not None:
                strcont = strcont + subcont.content + u";"

        hisrec = HistoryRecord(sch.tch_id, sch.tch_name, exist.id, exist.name, pbid, yytime, CONSULT_TODO, strcont, demands, datetime.datetime.now())
        if hisrec is not None:                
            db.session.add(hisrec)
            db.session.commit()
            exist.currecid = hisrec.id
            sch.currecid = hisrec.id
        else:
            msg = "预约咨询记录创建失败，请重新预约！"
            flash(msg)
            return redirect(url_for('get_teacher_schedule'))
            #return render_template('student_center.html',form=LoginForm())
            
        db.session.add(exist)
        db.session.add(sch) #更新咨询师表中预约标志
        
        db.session.commit()
        
        tostu = ""
        if sch.campus == DISTRICT_GULOU:
            tostu = SmsHandler.buildSmsMsg(contact, yytime+sch.tch_name+"咨询老师", 387203)
            msg = "已预约" + yytime + sch.tch_name + "咨询老师。" + "请提前5分钟到达鼓楼南园教学楼2楼西侧就业事务办理厅222等候咨询。"
        elif sch.campus == DISTRICT_XIANLIN:
            # 给老师和学生发送预定信息通知短信
            tostu = SmsHandler.buildSmsMsg(contact, yytime+sch.tch_name+"咨询老师", 390079)
            msg = "已预约" + yytime + sch.tch_name + "咨询老师。" + "请提前5分钟到达仙林校区第10食堂3楼学生就业事务办理厅326等候咨询。"
               
        #tostu = SmsHandler.buildSmsMsg(contact, sendmsgtostu, 388170)
        tch = Teacher.query.filter_by(seq=tcid).first()
        if tch is None:
            msg = "发生未知错误，咨询师编号无效，请联系管理员处理！"
            return render_template('msg_handler.html',msg=msg)
        totch = SmsHandler.buildSmsMsg(tch.contact, exist.name + "已经预约您" + yytime + "时段的咨询", 388170)

        p1=multiprocessing.Process(target=SendSmsMsg3,args=(totch,))
        p2=multiprocessing.Process(target=SendSmsMsg3,args=(tostu,))
        p1.start()
        p2.start()
        p1.join()
        p2.join()
            
        #给教师发送短信的模板号：
        #totch = SmsHandler.SendSmsMsg(tch.contact, exist.name + "已经预约您" + yytime + "时段的咨询", 388015)
        #totch = SmsHandler.SendSmsMsg(tch.contact, exist.name + "已经预约您" + yytime + "时段的咨询", 388015)
    elif sch is None:
        msg = "无此咨询师排班信息，请刷新后重新预约！"
    elif sch.isboot:
        msg = "咨询师显示已被他人预约，请刷新咨询排班信息！"
    else:
        msg = "未知错误，请刷新后重新预约！"

    #stu_logout_user(session, exist)

    flash(msg)
    return redirect(url_for('get_query_cancel_page'))

@app.route('/urgent_booking', methods=['GET', 'POST'])
def urgent_booking():
    notes = Notes.query.filter_by(type="紧急咨询版面").order_by(Notes.seq).all()
    return render_template('teacher_info_urgency.html',form=Form(),notes=notes)
    
@app.route('/get_query_cancel_page', methods=['GET', 'POST'])
@stu_login_required    
def get_query_cancel_page():
    contact = session.get('stu_contact')
    stu = Student.query.filter_by(contact=contact).first()
    
    if stu is None:
        msg = "未知错误，请重新登录！"
        stu_logout_user(session, stu)
        return render_template('msg_handler.html',msg=msg)
        
    hisrec = HistoryRecord.query.filter_by(stu_id=stu.id).order_by(HistoryRecord.id.desc()).all()
    hasBoot = False
    l = []
    if hisrec is not None:
        for item in hisrec: 
            di = {}
            di['seq'] = item.id
            di['date'] = item.date
            di['status'] = NjuUtility.GetYYStatus(item.status)
            di['tchname'] = item.tch_name
            di['content'] = item.content
            di['demands'] = item.demands
            di['feedback'] = ""
            if item.feedback is not None:
                di['feedback'] = item.feedback
            di['iscanl'] = False
            di['canlres'] = ""
            if item.status==CONSULT_CANCEL:
                di['iscanl'] = True
                di['canlres'] = item.s_can_res
            di['boottime'] = item.timestamp.strftime("%H:%M:%S")
            if stu.currecid == item.id:
                di['cancel'] = True
                hasBoot = True
            else:
                di['cancel'] = False
                
            #当前时间已经超过咨询开始时间则不允许学生端取消
            try:
                cur_time = datetime.datetime.now()
                consult_time = DateAndTimeHandler().YYTimeToDateTime(item.date)
                if cur_time > consult_time:
                    di['cancel'] = False
            except:
                pass
                
            di['href'] = ""
            di['pos'] = "仙林校区"
            tch = Teacher.query.filter_by(seq=item.tch_id).first()
            if tch is not None:
                di['href'] = tch.href
                if tch.position == DISTRICT_GULOU:
                    di['pos'] = "鼓楼校区"
            else:
                continue
                    
            l.append(di)
            
    return render_template('student_query.html', form=Form(), lists=l, hasBoot=hasBoot, cont=contact, name=stu.name)
    
@app.route('/get_student_cancel_page', methods=['GET', 'POST'])
@stu_login_required    
def get_student_cancel_page():
    contact = request.args.get('cont')
    yyseq = request.args.get('yyseq')
    
    return render_template('student_cancel.html',form=Form(),fpcont=contact,yyseq=yyseq)

@app.route('/cancel_book', methods=['GET','POST'])
@stu_login_required
def cancel_book():
    contact = request.form.get('contact')
    verycode = request.form.get('verycode')
    fd = request.form.get('feedback')
    yyseq = request.form.get('yyseq')
    
    msg = ""
    ret = False
    haserror = False

    try:
        if contact != session.get('stu_contact'):
            msg = "您的电话号码和预约学生的电话号码不同，无权取消！"
            haserror = True
        vcsi = session.get('very_code')
        if vcsi is None:
            msg = "请获取验证码并在其有效期内完成验证"
            haserror = True
        elif verycode != vcsi:
            msg = "您输入的验证码不正确，请重新输入"
            haserror = True
    except:
        msg = "验证过程出错，请重新验证"
        haserror = True
        
    if haserror:
        return jsonify(msg=msg, ret=ret)
        #flash(msg)
        #return render_template('student_cancel.html',form=Form(),fpcont=contact,yyseq=yyseq)
    
    stu = Student.query.filter_by(contact=contact).first()
    if stu is not None:
        hisrec = HistoryRecord.query.filter_by(id=stu.currecid).first()
        if hisrec is not None:
            consulttime = DateAndTimeHandler().YYTimeToDateTime(hisrec.date)
            if consulttime is None:
                msg = "预约记录的咨询时间格式有误，无法取消，请联系管理员处理！"
                haserror = True
            else:
                curtime = datetime.datetime.now()
                if (consulttime-curtime).days < 1:
                    msg = "咨询开始前24小时内不予取消！"
                    haserror = True
            
            if haserror:
                #flash(msg)
                #return render_template('student_cancel.html',form=Form(),fpcont=contact,yyseq=yyseq)
                return jsonify(msg=msg, ret=ret)
                
            sendmsgtostu = None
            sendmsgtotch = None

            sch = Schedule.query.filter_by(id=hisrec.pb_id).first()
            if sch is not None and sch.isboot:
                sch.isboot = 0
                sch.currecid = INVALID_TAG
                db.session.add(sch)
            
                msg = "已成功取消预约信息"
                yytime = DateAndTimeHandler().GetYYDateAndTime(sch.wkd_id)
                yytime = yytime + " " + sch.stm_id
                strpos = "仙林校区"
                if sch.campus == DISTRICT_GULOU:
                    strpos = "鼓楼校区"
                sendmsgtostu = "已取消"+yytime+strpos+sch.tch_name+"预约信息"
                sendmsgtotch = stu.name + "已经取消您" + yytime + "的咨询预约"

                stu.currecid = INVALID_TAG  #表示删除当前预约老师信息
                stu.canceltimes = stu.canceltimes+1
                if stu.canceltimes > CANCEL_TO_BLACK:
                    stu.status = STU_BLACK
                    msg = "已成功取消本次预约信息，由于取消次数过多，您已被限制使用预约平台！"
                db.session.add(stu)
                
                hisrec.status = CONSULT_CANCEL
                hisrec.s_can_res = fd
                hisrec.t_can_res = fd
                hisrec.timestamp = datetime.datetime.now()
                db.session.add(hisrec)
                
                db.session.commit()
                
                # 给老师和学生发送取消预定信息通知短信
                tostu = SmsHandler.buildSmsMsg(contact, sendmsgtostu, 388170)
                totch = SmsHandler.buildSmsMsg(sch.tch_contact, sendmsgtotch, 395043)

                p1=multiprocessing.Process(target=SendSmsMsg3,args=(totch,))
                p2=multiprocessing.Process(target=SendSmsMsg3,args=(tostu,))
                p1.start()
                p2.start()
                p1.join()
                p2.join()
                                
                #tostu = SmsHandler.SendSmsMsg(contact, sendmsgtostu, 388170)
                #给教师发送取消预约短信的模板号：万能模板号：388170
                #totch = SmsHandler.SendSmsMsg(sch.tch_contact, sendmsgtotch, 388170)
                
                ret = True
            elif sch is None:
                msg = "无咨询师信息，咨询师账号可能已被删除，请联系管理员处理！"
            else:
                msg = "该咨询师显示未被预约状态，请联系管理员处理！"
        else:
            msg = "未知错误，该用户无预约记录！"
    else:
        msg = contact+"用户不存在，注册预约成功才可以取消预约！"

    #flash(msg)
    return jsonify(msg=msg, ret=ret)
    #return redirect(url_for('get_query_cancel_page'))
 
##! 忘记密码修改 
@app.route('/forget_password', methods=['GET', 'POST'])
def forget_password():
    form = LoginForm()
    contact = form.contact.data
    if contact is None:
        return render_template('student_login.html',form=LoginForm(),coct="",copw="",corm="")
    else:
        stu = Student.query.filter_by(contact=contact).first()
        if stu is None:
            flash("用户不存在，请先预约咨询师")
            return render_template('index.html',form=Form())
    return render_template('modify_password.html',fpcont=contact)

@app.route('/modify_password', methods=['GET', 'POST'])
def modify_password():
    contact = request.form.get('contact')
    verycode = request.form.get('verycode')
    pw = request.form.get('password')
    
    status = "OK"
    msg=""
    if verycode != session.get('very_code'):
        status = "ERROR"
        msg="输入的验证码有误"
    else:
        stu_user = Student.query.filter_by(contact=contact).first()
        if stu_user is not None:
            stu_user.set_password(pw)
            db.session.add(stu_user)
            db.session.commit()
        else:
            status = "ERROR"
            msg="用户不存在，请核对手机号后重新输入"
        
    return jsonify(status=status,msg=msg)
    
@app.route('/tch_forget_password', methods=['GET', 'POST'])
def tch_forget_password():
    form = LoginForm()
    contact = form.contact.data
    if contact is None:
        return render_template('admin_teacher.html', form=form,coct="",copw="",corm="")
    else:
        tch = Teacher.query.filter_by(contact=contact).first()
        if tch is None:
            flash("用户不存在，请核对手机号后重新输入")
            return render_template('admin_teacher.html', form=form,coct="",copw="",corm="")
    return render_template('modify_tch_password.html',fpcont=contact)
    
@app.route('/modify_tch_password', methods=['GET', 'POST'])
def modify_tch_password():
    contact = request.form.get('contact')
    verycode = request.form.get('verycode')
    pw = request.form.get('password')
    
    status = "OK"
    msg=""
    if verycode != session.get('very_code'):
        status = "ERROR"
        msg="输入的验证码有误"
    else:
        tch_user = Teacher.query.filter_by(contact=contact).first()
        if tch_user is not None:
            tch_user.set_password(pw)
            db.session.add(tch_user)
            db.session.commit()
        else:
            status = "ERROR"
            msg="用户不存在，请核对手机号后重新输入"
        
    return jsonify(status=status,msg=msg)  
 
##! 验证码处理
@app.route('/verify_contact', methods=['GET','POST'])
@stu_login_required
def verify_contact():
    form = LoginForm()
    contact = form.contact.data
    verycode = form.password.data
    
    status = "OK"
    if verycode != session.get('very_code'):
        status = "ERROR"
    else:
        stu_user = Student.query.filter_by(contact=contact).first()
        stu_user.status = STU_NORMAL
        db.session.add(stu_user)
        db.session.commit()
        
    return jsonify(status=status,msg="")  
    
@app.route('/get_verify_code', methods=['GET','POST'])
@stu_login_required
def get_verify_code():
    form = LoginForm()
    contact = form.contact.data
    status = "OK"
    msg = ""
    if contact != session.get('stu_contact'):
        status = "ERROR"
        msg = "服务器发生错误，请重新登陆！"
        return jsonify(status=status,msg=msg)
    
    veryc = SmsHandler.createVerifyCode()
    session['very_code'] = veryc
    
    #发送登陆验证码，模板号：387837
    sendres = SmsHandler.SendSmsMsg(contact, veryc, 387837)
    
    #解析返回码
    #if sendres["stat"] != "100":
    #    status = "ERROR"
    #    msg = "服务器发送验证码失败，请重新操作！"
    
    return jsonify(status=status,msg=msg)
    
@app.route('/unlog_get_verify_code', methods=['GET','POST'])
def unlog_get_verify_code():
    contact = request.form.get('contact')
    
    if contact is None:
        return jsonify(status="ERROR",msg="您的手机号有误，请重新输入")
    
    status = "OK"
    msg = ""
    """
    if contact != session.get('stu_contact'):
        status = "ERROR"
        msg = "服务器发生错误，请重新登陆！"
        return jsonify(status=status,msg=msg)
    """
    veryc = SmsHandler.createVerifyCode()
    session['very_code'] = veryc
    
    #发送登陆验证码，模板号：387837
    sendres = SmsHandler.SendSmsMsg(contact, veryc, 387837)
    
    #解析返回码
    #if sendres["stat"] != "100":
    #    status = "ERROR"
    #    msg = "服务器发送验证码失败，请重新操作！"
    
    return jsonify(status=status,msg=msg)

##! 主页-教师入口
@app.route('/admin_teacher', methods=['GET', 'POST'])
def admin_teacher():
    form = LoginForm()
    cooname = request.cookies.get('tchname')
    coopw = request.cookies.get('tchpw')
    coorm = request.cookies.get('tchrm')
    if cooname is None:
        cooname,coopw,coorm = "","",""

    return render_template('admin_teacher.html', form=form,coct=cooname,copw=coopw,corm=coorm)
    
# 管理员、咨询师登陆操作
@app.route('/login', methods=['GET', 'POST'])
def login():
    #import pdb; pdb.set_trace()
    form = LoginForm()
    contact = form.contact.data
    pw = form.password.data
    rememberme = form.rememberme.data
    
    msg = ""

    if form.validate_on_submit():
        
        tch = Teacher.query.filter_by(contact=contact).first()

        if tch is not None:
            if tch.verify_password(pw):
                login_user(tch)
                response = None  
                
                if tch.role == ROLE_ADMIN:
                    response = make_response(render_template('admin.html',form = form, name=tch.name)) 
                elif tch.role == ROLE_TEACHER:
                    response = make_response(render_template('teacher_center.html',form = form, name=tch.name)) 
                    
                if rememberme is True:
                    response.set_cookie('tchname',value=contact,max_age=COOKIE_VALID_PERIOD)
                    response.set_cookie('tchpw',value=tch.password,max_age=COOKIE_VALID_PERIOD)
                    response.set_cookie('tchrm',value="checked",max_age=COOKIE_VALID_PERIOD)
                else:
                    response.set_cookie('tchname',value=contact,max_age=0)
                    response.set_cookie('tchpw',value=tch.password,max_age=0)
                    response.set_cookie('tchrm',value="checked",max_age=0)
                    
                return response
            else:
                msg = "您输入的密码有误，请确认后重新输入"
                flash(msg)
        else:
            msg = "用户不存在，请联系管理员进行处理"
            flash(msg)
    
    cooname = request.cookies.get('tchname')
    coopw = request.cookies.get('tchpw')
    coorm = request.cookies.get('tchrm')
    if cooname is None:
        cooname,coopw,coorm = "","",""
          
    return render_template('admin_teacher.html', form=form,coct=cooname,copw=coopw,corm=coorm)

@app.route('/logout', methods=['GET', 'POST'])
@login_required
def logout():
    __OutDebugInfo("logout")
    logout_user()
    
    cooname = request.cookies.get('tchname')
    coopw = request.cookies.get('tchpw')
    coorm = request.cookies.get('tchrm')
    if cooname is None:
        cooname,coopw,coorm = "","",""
    return render_template('admin_teacher.html', form=LoginForm(),coct=cooname,copw=coopw,corm=coorm)

@app.route('/admin_center',methods=['GET','POST'])
@login_required
def admin_center():
    return render_template('admin.html',form = LoginForm(), name=current_user.name)
##############################

#管理员操作

# 重置所有排班信息
@app.route('/reset_all_book_info', methods=['GET', 'POST'])
@login_required
def reset_all_book_info():
    msg = ""
    curday = datetime.datetime.now()
    today = curday.weekday()
    if today!=calendar.FRIDAY and False:
        msg = "只允许在每周星期五重置预约排班信息"
        flash(msg)
        return render_template('admin.html',form = LoginForm(), name=current_user.name)
        
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            schs = Schedule.query.all()
            try:
                for item in schs:
                    if item.isboot or item.currecid!=INVALID_TAG:
                        
                        hisrec = HistoryRecord.query.filter_by(id=item.currecid).first()
                        if hisrec is not None:
                            hisrec.status = CONSULT_COMPLETE
                            db.session.add(hisrec)
                            
                            stu = Student.query.filter_by(currecid=item.currecid).first()
                            if stu is not None:
                                stu.currecid = INVALID_TAG
                                db.session.add(stu)

                        item.isboot = 0
                        item.currecid=INVALID_TAG
                        db.session.add(item)
                db.session.commit()

                msg = "已重置所有排班为可预约状态"
            except Exception as e:
                msg = "重置所有排班过程中出现错误"
                flash(str(e))
                pass
        else:
            msg = "您无权操作"
    else:
        msg = "未知错误"
    flash(msg)
    return render_template('admin.html',form = LoginForm(), name=current_user.name)
    
@app.route('/enable_all_book_info', methods=['GET', 'POST'])
@login_required
def enable_all_book_info():
    msg = ""
        
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            try:
                if current_user.imgname is None:
                    msg = "暂未设置不可预约的时段"
                else:
                    current_user.imgname = None
                    db.session.commit()
                    msg = "已取消设定时段的不可预约状态"
            except Exception as e:
                msg = "取消不可预约状态过程中出现错误"
                flash(str(e))
                pass
        else:
            msg = "您无权操作"
    else:
        msg = "未知错误"
    flash(msg)
    return render_template('admin.html',form = LoginForm(), name=current_user.name)
    
@app.route('/disable_all_book_info', methods=['GET', 'POST'])
@login_required
def disable_all_book_info():
    begindate = request.args.get('begindate')
    enddate = request.args.get('enddate')
    bl = None
    el = None
    disb = None
    dise = None
    try:
        bl = begindate.split('-')
        disb = datetime.datetime(int(bl[0]),int(bl[1]),int(bl[2]))
        el = enddate.split('-')
        dise = datetime.datetime(int(el[0]),int(el[1]),int(el[2]))
    except Exception as e:
        flash("起止时间区间格式有误:"+str(e))
        return render_template("admin_view_option.html",disabled_interval="", form=Form())
    
    msg = ""
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            
            try:
                current_user.imgname = begindate+":"+enddate
                db.session.commit()
                msg = "已设置"+disb.strftime("%Y-%m-%d")+"到"+dise.strftime("%Y-%m-%d")+"时间段为不可预约状态"
            except Exception as e:
                msg = "重置所有排班过程中出现错误"
                flash(str(e))
                pass
        else:
            msg = "您无权操作"
    else:
        msg = "未知错误"
    flash(msg)
    return render_template('admin.html',form = LoginForm(), name=current_user.name)


# 1.添加教师
@app.route('/regester_teacher_data', methods=['GET', 'POST'])
@login_required
def regester_teacher_data():
    seq = request.form.get('s')
    name = request.form.get('a')
    contact = request.form.get('b')
    dateseq = request.form.get('c')
    timeseq = request.form.get('d')
    email = request.form.get('e')
    password = request.form.get('f')
    href = request.form.get('g')
    pos = request.form.get('h')

    msg = ""
    result = "1"

    try:
        tc_pos = DISTRICT_XIANLIN
        if str(pos) == "2":
            tc_pos = DISTRICT_GULOU
        new_tch = Teacher.query.filter_by(contact=contact).first()
        if new_tch is None:
            new_tch = Teacher(seq,name,contact,email,password,None,tc_pos,u'',None,ROLE_TEACHER,datetime.datetime.now())
            db.session.add(new_tch)
            db.session.commit()
            msg = "已成功添加咨询师：" + name
        else:
            msg = "已为"+new_tch.name+"更新排班信息"
            
        sch = Schedule(new_tch.seq,new_tch.name,new_tch.contact,dateseq,timeseq,tc_pos,datetime.datetime.now())
        db.session.add(sch)
        db.session.commit()
        
        result = "0"
            
    except Exception as e:
        msg = str(e)
        pass

    return jsonify(result=result,msg=msg)

@app.route("/admin_edit_slider", methods=['GET','POST'])
@login_required
def admin_edit_slider():
    id = request.args.get('id')
    if id:
        article = Slider.query.filter_by(id=id).first()
        if article is None:
            article = Slider('', '/static/tchimgs/main_icon.jpg', '', datetime.datetime.now())
            article.id = '';
    else:
        article = Slider('', '/static/tchimgs/main_icon.jpg', '', datetime.datetime.now())
        article.id = '';
    return render_template('admin_edit_slider.html',article=article)

@app.route("/admin_edit_article", methods=['GET','POST'])
@login_required
def admin_edit_article():
    id = request.args.get('id')
    if id:
        article = Article.query.filter_by(id=id).first()
        if article is None:
            article = Article('', '','/static/tchimgs/main_icon.jpg', '', '', 1, '', datetime.datetime.now())
            article.id = '';
    else:
        article = Article('','','/static/tchimgs/main_icon.jpg','','',1,'',datetime.datetime.now())
        article.id = '';
    return render_template('admin_edit_article.html',article=article)

@app.route("/admin_article_lists", methods=['GET','POST'])
@login_required
def admin_article_lists():
    stus = Article.query.order_by(Article.id.desc()).all()
    li_ = []
    if stus is not None:
        for item in stus:
            di = {}
            di['id'] = item.id
            di['title'] = item.title
            di['description'] = item.description
            di['date'] = item.date
            di['img'] = "/static/article/" + item.img
            di['link'] = item.link
            di['type'] = NjuUtility.GetArticleType(item.type)
            li_.append(di)
    return render_template("admin_article_lists.html", lists=li_, form=Form())

@app.route("/admin_slider_lists", methods=['GET','POST'])
@login_required
def admin_slider_lists():
    stus = Slider.query.order_by(Slider.id.desc()).all()
    li_ = []
    if stus is not None:
        for item in stus:
            di = {}
            di['id'] = item.id
            di['title'] = item.title
            di['img'] = "/static/article/" + item.img
            di['link'] = item.link
            li_.append(di)
    return render_template("admin_slider_lists.html", lists=li_, form=Form())

@app.route('/delete_article', methods=['GET', 'POST'])
@login_required
def delete_article():
    id = request.form.get('id')
    print id
    article = Article.query.filter_by(id=id).first()
    db.session.delete(article)
    db.session.commit()
    msg = "成功删除文章"
    return jsonify(result="", msg=msg)

@app.route('/delete_slider', methods=['GET', 'POST'])
@login_required
def delete_slider():
    id = request.form.get('id')
    print id
    article = Slider.query.filter_by(id=id).first()
    db.session.delete(article)
    db.session.commit()
    msg = "成功删除"
    return jsonify(result="", msg=msg)

# 1.添加文章
@app.route('/admin_edit_article_submit', methods=['GET', 'POST'])
@login_required
def admin_edit_article_submit():
    title = request.form.get('title')
    description = ""
    date = request.form.get('date')
    img = request.form.get('img')
    type = request.form.get('type')
    link = request.form.get('link')
    create_time = datetime.datetime.now()
    id = request.form.get('id')
    msg = ""
    result = "1"
    try:
        if id:
            article = Article.query.filter_by(id=id).first()
            if article is not None:
                article.title = title
                article.description = description
                article.img = img
                article.date = date
                article.type = type
                article.link = link
                db.session.add(article)
                db.session.commit()
        else:
            new_article = Article(title, description, img, date, "", type, link, create_time)
            db.session.add(new_article)
            db.session.commit()
        result = "0"

    except Exception as e:
        msg = str(e)
        pass

    return jsonify(result=result, msg=msg)

@app.route('/admin_edit_slider_submit', methods=['GET', 'POST'])
@login_required
def admin_edit_slider_submit():
    title = request.form.get('title')
    img = request.form.get('img')
    link = request.form.get('link')
    create_time = datetime.datetime.now()
    id = request.form.get('id')
    msg = ""
    result = "1"
    try:
        if id:
            article = Slider.query.filter_by(id=id).first()
            if article is not None:
                article.title = title
                article.img = img
                article.link = link
                db.session.add(article)
                db.session.commit()
        else:
            new_article = Slider(title, img, link, create_time)
            db.session.add(new_article)
            db.session.commit()
        result = "0"

    except Exception as e:
        msg = str(e)
        pass

    return jsonify(result=result, msg=msg)

@app.route('/admin_get_teacher_lists', methods=['GET', 'POST'])
@login_required
def admin_get_teacher_lists():
    tchs = Teacher.query.filter_by(role=ROLE_TEACHER).all()
    li_ = []
    if tchs is not None:
        for item in tchs:
            di = {}
            di['id']=item.seq
            di['name']=item.name
            di['contact'] = item.contact
            di['email'] = item.email
            di['href'] = item.href
            
            schs = Schedule.query.filter_by(tch_id=item.seq).all()
            pbs = []
            for elem in schs:
                pbinfo = {}
                pbinfo['pbid'] = elem.id
                pbinfo['dateseq'] = DateAndTimeHandler().toDateSeqStr(elem.wkd_id)
                pbinfo['timeseq'] = elem.stm_id
                pbinfo['isboot'] = elem.isboot
                pbinfo['pos'] = elem.campus
            
                sub_name = "无"
                if elem.isboot:
                    hisrec = HistoryRecord.query.filter_by(id=elem.currecid).first()
                    if hisrec is not None:
                        sub_name = hisrec.stu_name
                    else:
                        continue

                pbinfo['subname'] = sub_name
                pbs.append(pbinfo)
                
            di['pbinfo'] = pbs
            
            li_.append(di)
            
    return render_template("admin_view_teacher.html", lists=li_, form=LoginForm())
    
@app.route('/admin_set_teacher_page', methods=['GET', 'POST'])
@login_required
def admin_set_teacher_page():
    if current_user is not None:
        val = request.args.get('val')
        
        li_ = {}
        tch =  Teacher.query.filter_by(seq=val).first()
        if tch is None:
            flash("您所要修改的咨询师ID非法")
        elif val is None:
            flash("您所要修改的咨询师ID参数非法，请重新操作")
        else:
            li_['id'] = tch.seq
            li_['name'] = tch.name
            li_['contact'] = tch.contact
            li_['image'] = "/static/tchimgs/"+(tch.imgname if tch.imgname is not None else "main_icon.jpg")
            li_['intro'] = tch.intro
            disint = ""
            if tch.href is not None:
                try:
                    begint = tch.href.split(':')[0]
                    endint = tch.href.split(':')[1]
                    disint = "不可预约时间段："+begint+"到"+endint
                except:
                    pass
            else:
                pass
        
            return render_template("admin_set_teacher.html", li=li_, disabled_interval=disint, form=LoginForm())
    
    return render_template('msg_handler.html',msg="未知错误，请重新登录")
    
# 2.查看教师 rejected
@app.route('/view_tch_data', methods=['GET', 'POST'])
@login_required
def view_tch_data():
    __OutDebugInfo("view_tch_data")
    tchs = Teacher.query.filter_by(role=ROLE_TEACHER).all()
    li_ = []
    if tchs is not None:
        for item in tchs:
            di = {}
            di['id']=item.seq
            di['name']=item.name
            di['contact'] = item.contact
            di['email'] = item.email
            di['href'] = item.href
            
            schs = Schedule.query.filter_by(tch_id=item.seq).all()
            pbs = []
            for elem in schs:
                pbinfo = {}
                pbinfo['pbid'] = elem.id
                pbinfo['dateseq'] = DateAndTimeHandler().toDateSeqStr(elem.wkd_id)
                pbinfo['timeseq'] = elem.stm_id
                pbinfo['isboot'] = elem.isboot
                pbinfo['pos'] = elem.position
            
                sub_name = "无"
                if item.isboot:
                    hisrec = HistoryRecord.query.filter_by(id=elem.currecid).first()
                    if hisrec is not None:
                        sub_name = hisrec.stu_name
                    else:
                        continue

                pbinfo['subname'] = sub_name
                pbs.append(pbinfo)
                
            di['pbinfo'] = pbs
                
            li_.append(di)
 
    return json.dumps(li_)

# 3.删除咨询师用户
@app.route('/delete_teacher', methods=['GET', 'POST'])
@login_required
def delete_teacher():
    tcid = request.form.get('tcid')
    tch = Teacher.query.filter_by(seq=int(tcid)).first()
    msg = ""
    name = ""

    if tch is not None:
        msg = "成功删除教师用户 " + tch.name + " 及其所有排班"
        try:
            schs = Schedule.query.filter_by(tch_id=tch.seq).all()
            for item in schs:
                if item.isboot or item.currecid!=INVALID_TAG:
                    hisrec = HistoryRecord.query.filter_by(id=item.currecid).first()
                    if hisrec is not None:
                        hisrec.status = CONSULT_COMPLETE
                        db.session.add(hisrec)
                        
                        stu = Student.query.filter_by(currecid=item.currecid).first()
                        if stu is not None:
                            stu.currecid = INVALID_TAG
                            db.session.add(stu)

                    item.isboot = 0
                    item.currecid=INVALID_TAG
                    
                db.session.delete(item)
                db.session.commit()

            db.session.delete(tch)
            db.session.commit()
        except Exception as e:
            msg = str(e)
    else:
        msg = "您要删除的用户不存在！"
    
    return jsonify(result="", msg=msg)
    
@app.route('/delete_teacher_pbinfo', methods=['GET', 'POST'])
@login_required
def delete_teacher_pbinfo():
    pbid = request.form.get('pbid')
    sch = Schedule.query.filter_by(id=pbid).first()
    if sch is not None:
        if sch.isboot:
            msg="您要删除的排班已经被预约，请先取消学生预约记录"+str(sch.currecid)+"再删除！"
        else:
            msg = "成功删除排班信息"
            db.session.delete(sch)
            db.session.commit()
    else:
        msg = "您要删除的排班信息不存在"
        
    return jsonify(msg=msg)
  
def allowed_image_file(filename):
    return '.' in filename and \
        filename.rsplit('.', 1)[1] in ALLOWED_IMAGE_EXT   
        
@app.route('/upload_tch_image', methods=['GET', 'POST'])
@login_required
def upload_tch_image():  
    tcid = request.args.get('tcid')
    cont = request.args.get('cont')
    
    if request.method == 'POST':
        from werkzeug import secure_filename
        import os.path
        try:
            file = request.files['file']
            if file and allowed_image_file(file.filename):
                filename = secure_filename(file.filename)
                filename = 'img_'+tcid+'_'+cont+os.path.splitext(filename)[1]
                tch = Teacher.query.filter_by(contact=cont).first()

                if tch is not None:
                    tch.imgname = filename
                    db.session.add(tch)
                    db.session.commit()
  
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], '..', 'app','static', 'tchimgs', filename))
                flash("上传图片成功")
            else:
                flash("不支持的图片格式，请重新上传")
        except Exception as e:
            print(str(e))
            pass
            
    return redirect(url_for('admin_set_teacher_page',val=tcid,next=request.url))
    
@app.route('/add_tch_intro', methods=['GET', 'POST'])
@login_required
def add_tch_intro():
    intro = request.form.get('intro')
    tcid = request.form.get('tcid')
    
    status = True
    try:
        tch = Teacher.query.filter_by(seq=tcid).first()
        if tch is not None:
            tch.intro = intro
            db.session.add(tch)
            db.session.commit()
    except:
        status = False
        
    return jsonify(status=status)  

@app.route('/add_tch_link', methods=['GET', 'POST'])
@login_required
def add_tch_link():
    cont = request.form.get('contact')
    link = request.form.get('link')
    tch = Teacher.query.filter_by(seq=int(cont)).first()
    msg = ""

    if tch is not None and link!="":
        name = tch.name
        tch.href = link
        db.session.add(tch)
        db.session.commit()
        msg = "成功为教师用户" + name + "添加链接"
    elif tch is None:
        msg = "您要修改的用户不存在！"
    elif link=="":
        msg = "您输入的链接为空"
    
    return jsonify(result="", msg=msg)  

@app.route('/add_tch_pb', methods=['GET', 'POST'])
@login_required
def add_tch_pb():
    tcid = request.form.get('tcid')
    wkpb = request.form.get('wkpb')
    tmpb = request.form.get('tmpb')
    
    ret = False
    msg = "已成功添加咨询时排班"
    
    tch = Teacher.query.filter_by(seq=tcid).first()
    if tch is not None:
        try:
            sch = Schedule(tch.seq,tch.name,tch.contact,wkpb,tmpb,tch.position,datetime.datetime.now())
            if sch is not None:
                db.session.add(sch)
                db.session.commit()
        except:
            pass
    else:
        msg = "您所要添加的咨询师用户不存在"
        pass
    
    return jsonify(ret=ret) 
    
@app.route('/add_tch_booking', methods=['GET', 'POST'])
@login_required
def add_tch_booking():
    tcid = request.form.get('tcid')
    
    msg = "已成功取消不可预约时段"
    ret = False
    
    tch = Teacher.query.filter_by(seq=tcid).first()
    if tch is not None:
        try:
            tch.href = None
            db.session.add(tch)
            db.session.commit()
            ret = True
        except:
            msg = str(e)
            pass
    else:
        msg = "您所要设置的咨询师用户不存在"
        pass
    
    return jsonify(ret=ret,msg=msg)
    
@app.route('/add_tch_unbooking', methods=['GET', 'POST'])
@login_required
def add_tch_unbooking():
    tcid = request.form.get('tcid')
    begindate = request.form.get('begindate')
    enddate = request.form.get('enddate')
    
    msg = "已成功设置不可预约时段"
    ret = False
    
    bl = None
    el = None
    disb = None
    dise = None
    try:
        bl = begindate.split('-')
        disb = datetime.datetime(int(bl[0]),int(bl[1]),int(bl[2]))
        el = enddate.split('-')
        dise = datetime.datetime(int(el[0]),int(el[1]),int(el[2]))
    except Exception as e:
        msg = "起止时间区间格式有误:"+str(e)
        return jsonify(ret=ret,msg=msg)
    
    tch = Teacher.query.filter_by(seq=tcid).first()
    if tch is not None:
        try:
            tch.href = begindate+":"+enddate
            db.session.add(tch)
            db.session.commit()
            ret = True
        except:
            msg = str(e)
            pass
    else:
        msg = "您所要设置的咨询师用户不存在"
        pass
    
    return jsonify(ret=ret,msg=msg)
    
@app.route('/admin_get_student_lists', methods=['GET', 'POST'])
@login_required
def admin_get_student_lists():
    stus = Student.query.all()
    li_ = []
    if stus is not None:
        for item in stus:
            di = {}
            di['id']=item.id
            di['name']=item.name
            di['sex']=item.sex
            di['contact'] = item.contact
            di['department'] = item.department
            di['major'] = item.major
            di['grade'] = item.grade
            di['stuID'] = item.stuID
            di['status'] = NjuUtility.GetStuStatus(item.status)
            di['cans'] = item.canceltimes
    
            hisrec = HistoryRecord.query.filter_by(id=item.currecid).first()
            if hisrec is not None:
                di['date'] = hisrec.date
                di['tchname'] = hisrec.tch_name
            else:
                di['date'] = "-"
                di['tchname'] = "无"
        
            li_.append(di)
            
    return render_template("admin_view_student.html", lists=li_,form=Form())        

# 4.显示学生用户 rejected
@app.route('/view_stu_data', methods=['GET', 'POST'])
@login_required
def view_stu_data():
    __OutDebugInfo("view_stu_data")
    stus = Student.query.all()
    li_ = []
    if stus is not None:
        for item in stus:
            di = {}
            di['name']=item.name
            di['sex']=item.sex
            di['contact'] = item.contact
            #di['email'] = item.email
            di['department'] = item.department
            di['major'] = item.major
            di['grade'] = item.grade
            di['stuID'] = item.stuID
            di['status'] = item.status
            di['cans'] = item.canceltimes
    
            hisrec = HistoryRecord.query.filter_by(id=item.currecid).first()
            if hisrec is not None:
                di['date'] = hisrec.date
                di['tchname'] = hisrec.tch_name
            else:
                di['date'] = "-"
                di['tchname'] = "无"
        
            li_.append(di)

    return json.dumps(li_)
    
@app.route('/admin_get_records_page', methods=['GET', 'POST'])
@login_required
def admin_get_records_page():   
    return render_template("admin_records.html", form=LoginForm())
 
@app.route('/admin_get_records', methods=['GET', 'POST'])
@login_required
def admin_get_records():
    begindate = request.args.get('begindate')
    enddate = request.args.get('enddate')
    bl = None
    el = None
    db = None
    de = None
    try:
        bl = begindate.split('-')
        db = datetime.datetime(int(bl[0]),int(bl[1]),int(bl[2]))
        el = enddate.split('-')
        de = datetime.datetime(int(el[0]),int(el[1]),int(el[2]))
    except Exception as e:
        flash("起止时间区间格式有误:"+str(e))
        return render_template("admin_records.html", form=LoginForm())
    
    hisrec = HistoryRecord.query.all()
    li_ = []
    if hisrec is not None:
        for item in hisrec:
            cl = None
            consulttime = None
            try:
                cl = item.date.split('-')
                consulttime = datetime.datetime(int(cl[0]),int(cl[1]),int(cl[2]))
            except Exception as e:
                if item.pb_id!=INVALID_TAG:
                    flash("起止时间区间格式有误:"+str(e))
                    return render_template("admin_records.html", form=LoginForm())
                else:
                    continue
            #consulttime = DateAndTimeHandler().YYTimeToDateTime(item.date)
            if consulttime is None:
                flash("出现内部错误，预约记录"+str(item.id)+"的咨询时间格式错误")
                continue
                #return render_template("admin_records.html", form=LoginForm())
            else:
                if consulttime<db or consulttime>de:
                    continue
            di = {}
            di['seq'] = item.id
            di['tchname']=item.tch_name
            di['tchid']=item.tch_id
            di['stuname']=item.stu_name
            di['stuid']=item.stu_id
            di['date'] = item.date
            di['status'] = NjuUtility.GetYYStatus(item.status)
            di['content'] = item.content
            di['demands'] = item.demands
            di['feedback'] = item.feedback
            di['timestamp'] = item.timestamp.strftime("%H:%M:%S")
            di['replace'] = False
            if item.status==CONSULT_TODO:
                di['replace'] = True
        
            li_.append(di)
            
    return render_template("admin_view_records.html", form=Form(),lists=li_)
    
@app.route('/admin_cancel_record', methods=['GET', 'POST'])
@login_required
def admin_cancel_record():
    seq = request.form.get('seq')
    rp_rs = u"系统管理员取消咨询预约"
    
    msg = ""
    ret = False
    
    hisrec = HistoryRecord.query.filter_by(id=seq).first()
    if hisrec is None:
        msg = "待取消的咨询记录编号非法"
        return jsonify(ret=ret,msg=msg)
        
    if hisrec.status == CONSULT_CANCEL:
        msg = "该咨询记录已经被取消"
        return jsonify(ret=ret,msg=msg)
        
    try:
        hisrec.status = CONSULT_CANCEL
        hisrec.s_can_res = rp_rs
        hisrec.t_can_res = rp_rs
        db.session.add(hisrec)
        
        sch = Schedule.query.filter_by(id=hisrec.pb_id).first()
        if sch is not None and sch.isboot:
            sch.isboot = 0
            sch.currecid = INVALID_TAG
            db.session.add(sch)
        elif sch is None:
            msg = "预约记录所对应的排班信息不存在"
            #return jsonify(msg=msg,ret=ret)
        else:
            msg = "预约记录所对应的排班显示未被预约"
            return jsonify(msg=msg,ret=ret)
        
        stu = Student.query.filter_by(currecid=hisrec.id).first()
        if stu is not None:
            stu.currecid = INVALID_TAG
            db.session.add(stu)
            db.session.commit()
        else:
            pass
        msg = "取消预约咨询成功"
        ret = True
    except Exception as e:
        msg = "取消咨询预约记录时出错"+str(e)
        pass
    
    return jsonify(ret=ret,msg=msg)

@app.route('/admin_set_replace', methods=['GET', 'POST'])
@login_required
def admin_set_replace():
    seq = request.form.get('seq')
    rp_seq = request.form.get('rp_seq')
    rp_rs = request.form.get('rp_rs')
    
    msg = ""
    ret = False
    
    hisrec = HistoryRecord.query.filter_by(id=seq).first()
    if hisrec is None:
        msg = "待替换的咨询记录编号非法"
        return jsonify(ret=ret,msg=msg)
        
    tch_rp = Teacher.query.filter_by(seq=rp_seq).first()
    if tch_rp is None:
        msg = "顶替咨询师编号非法"
        return jsonify(ret=ret,msg=msg)
    
    try:
        hisrec.status = CONSULT_CANCEL
        hisrec.s_can_res = rp_rs
        hisrec.t_can_res = rp_rs
        db.session.add(hisrec)
        
        new_hisrec = HistoryRecord(tch_rp.seq, tch_rp.name, hisrec.stu_id, hisrec.stu_name, hisrec.pb_id, hisrec.date, CONSULT_TODO, hisrec.content, hisrec.demands,datetime.datetime.now())
        db.session.add(new_hisrec)
        db.session.commit()
        
        stu = Student.query.filter_by(currecid=hisrec.id).first()
        if stu is not None:
            stu.currecid = new_hisrec.id
            db.session.add(stu)
            db.session.commit()
        else:
            pass
        msg = "设置顶替排班成功"
        ret = True
    except:
        msg = "创建新的咨询预约记录时出错"
        pass
        
    return jsonify(ret=ret,msg=msg)
    
# 4.显示预约信息 rejected
@app.route('/view_rec_data', methods=['GET', 'POST'])
@login_required
def view_rec_data():
    __OutDebugInfo("view_rec_data")

    hisrec = HistoryRecord.query.all()
    li_ = []
    if hisrec is not None:
        for item in hisrec:
            di = {}
            di['seq'] = item.id
            di['tchname']=item.tch_name
            di['tchid']=item.tch_id
            di['stuname']=item.stu_name
            di['stuid']=item.stu_id
            di['date'] = item.date
            di['status'] = NjuUtility.GetYYStatus(item.status)
            di['content'] = item.content
            di['demands'] = item.demands
            di['feedback'] = item.feedback
            di['timestamp'] = item.timestamp.strftime("%H:%M:%S")
        
            li_.append(di)

    return json.dumps(li_)
    
@app.route('/admin_get_options', methods=['GET', 'POST'])
@login_required
def admin_get_options():
    admin = Teacher.query.filter_by(role=ROLE_ADMIN).first()
    disint = ""
    
    try:
        begint = admin.imgname.split(':')[0]
        endint = admin.imgname.split(':')[1]
        disint = "不可预约时间段："+begint+"到"+endint
    except:
        pass
    
    return render_template("admin_view_option.html",disabled_interval=disint,form=Form())

# 添加可预约时间段
@app.route('/add_time_opt', methods=['GET', 'POST'])
@login_required
def add_time_opt():
    stm = request.form.get('stm')
    
    ret = False
    try:
        if stm is not None and stm!="":
            subtime = SubsTime(stm,datetime.datetime.now())
            db.session.add(subtime)
            db.session.commit()
            ret = True
    except:
        pass
        
    return jsonify(ret=ret)

# 删除可预约时间段
@app.route('/del_time_opt', methods=['GET', 'POST'])
@login_required
def del_time_opt():
    stm = request.form.get('stm')
    
    ret = False
    subtime = SubsTime.query.filter_by(stm=stm).first()
    if subtime is not None:
        db.session.delete(subtime)
        db.session.commit()
        ret = True
        
    return jsonify(ret=ret)
    
# 添加可咨询内容项
@app.route('/add_cont_opt', methods=['GET', 'POST'])
@login_required
def add_cont_opt():
    cont = request.form.get('cont')
    
    ret = False
    try:
        if cont is not None and cont!="":
            subcont = SubsContent(cont,datetime.datetime.now())
            db.session.add(subcont)
            db.session.commit()
            ret = True
    except:
        pass
        
    return jsonify(ret=ret)
    
# 添加可咨询注意事项
@app.route('/add_notes_opt', methods=['GET', 'POST'])
@login_required
def add_notes_opt():
    notes_seq = request.form.get('notes_seq')
    notes = request.form.get('notes')
    type = request.form.get('type')
    
    ret = False
    try:
        if notes_seq is not None and notes_seq!="":
            notes = Notes(notes_seq,notes,type,datetime.datetime.now())
            db.session.add(notes)
            db.session.commit()
            ret = True
    except Exception as e:
        print(str(e))
        pass
        
    return jsonify(ret=ret)
    
# 添加咨询师链接
@app.route('/add_teachers_link', methods=['GET', 'POST'])
@login_required
def add_teachers_link():
    link = request.form.get('link')
    
    ret = False
    try:
        tch = current_user
        if tch.role == ROLE_ADMIN:
            tch.href = link
            db.session.add(tch)
            db.session.commit()
            ret = True
    except:
        pass
        
    return jsonify(ret=ret)

# 删除可咨询内容项
@app.route('/del_cont_opt', methods=['GET', 'POST'])
@login_required
def del_cont_opt():
    cont = request.form.get('cont')
    
    ret = False
    subcont = SubsContent.query.filter_by(content=cont).first()
    if subcont is not None:
        db.session.delete(subcont)
        db.session.commit()
        ret = True
    
    return jsonify(ret=ret)
    
# 删除咨询注意事项
@app.route('/del_notes_opt', methods=['GET', 'POST'])
@login_required
def del_notes_opt():
    noteid = request.form.get('noteid')
    
    ret = False
    notes = Notes.query.filter_by(id=noteid).first()
    if notes is not None:
        db.session.delete(notes)
        db.session.commit()
        ret = True
    
    return jsonify(ret=ret)

# 已有咨询内容项
@app.route('/existed_cont', methods=['GET', 'POST'])
@login_required
def existed_cont():
    subcont = SubsContent.query.all()
    l = []
    if subcont is not None:
        for item in subcont:
            di = {}
            di['id'] = item.id
            di['cont'] = item.content
            l.append(di)
            
    return json.dumps(l)
    
@app.route('/stu_existed_cont', methods=['GET', 'POST'])
def stu_existed_cont():
    subcont = SubsContent.query.all()
    l = []
    if subcont is not None:
        for item in subcont:
            di = {}
            di['id'] = item.id
            di['cont'] = item.content
            l.append(di)
            
    return json.dumps(l)

# 已有可预约时间段
@app.route('/existed_time', methods=['GET', 'POST'])
@login_required
def existed_time():
    subtimes = SubsTime.query.all()
    l = []
    if subtimes is not None:
        for item in subtimes:
            di = {}
            di['id'] = item.id
            di['stm'] = item.stm
            l.append(di)
            
    return json.dumps(l)
    
# 已有咨询注意事项
@app.route('/existed_notes', methods=['GET', 'POST'])
@login_required
def existed_notes():
    notes = Notes.query.all()
    l = []
    if notes is not None:
        for item in notes:
            di = {}
            di['id'] = item.id
            di['seq'] = item.seq
            di['content'] = item.content
            di['type'] = item.type
            l.append(di)
            
    return json.dumps(l)
 
# 修改教师排班时间段
@app.route('/modify_tch_pb', methods=['GET', 'POST'])
@login_required
def modify_tch_pb(): 
    contact = request.form.get('contact')
    wkd = request.form.get('wkd')
    stm = request.form.get('stm')
    
    ret = False
    """
    tch = Teacher.query.filter_by(seq=int(contact)).first()
    if tch is not None:
        tch.wkd_id = wkd
        tch.stm_id = stm
        db.session.add(tch)
        db.session.commit()
        ret = True
    """
    return jsonify(ret=ret)

# 删除学生用户
@app.route('/delete_student', methods=['GET', 'POST'])
@login_required
def delete_student():
    __OutDebugInfo("delete student")
    cont = request.form.get('contact')
    stu = Student.query.filter_by(contact=cont).first()

    msg = ""
    name = ""
    if stu is not None:
        name = stu.name

        if stu.currecid != INVALID_TAG: #已经预约成功，需要先取消预约信息
            sch = Schedule.query.filter_by(currecid=stu.currecid).first()
            if sch is not None:
                sch.isboot = 0
                sch.currecid = INVALID_TAG
                db.session.add(sch)
                
                hisrec = HistoryRecord.query.filter_by(id=stu.currecid).first()
                if hisrec is not None:
                    hisrec.status = CONSULT_CANCEL
                    db.session.add(hisrec)
            
        db.session.delete(stu)
        db.session.commit()
        msg = "成功删除学生用户：" + name
    else:
        msg = "您要删除的用户不存在！"

    return jsonify(result="", msg=msg)

    
##############################

# 咨询师操作
@app.route('/get_teacher_center', methods=['GET','POST'])
@login_required
def get_teacher_center():
    tch = current_user
    if tch is None:
        msg = "未知错误，请重新登录！"
        return render_template('msg_handler.html',msg=msg)
        
    return render_template('teacher_center.html', form=LoginForm(), name=tch.name)

@app.route('/get_teacher_query_cancel_page', methods=['GET', 'POST'])
@login_required    
def get_teacher_query_cancel_page():
    tch = current_user
    if tch is None:
        msg = "未知错误，请重新登录！"
        return render_template('msg_handler.html',msg=msg)
    
    li_ = []
    teachers = Teacher.query.filter_by(contact=tch.contact).all()
    for elem in teachers:
        hisrecs = HistoryRecord.query.filter_by(tch_id=elem.seq).order_by(HistoryRecord.id.desc()).all()
        if hisrecs is not None:
            for item in hisrecs:
                di = {}
                di['recid'] = item.id
                di['name']=item.stu_name
                di['stuno']=item.stu_id
                di['status'] = NjuUtility.GetYYStatus(item.status)
                di['date'] = item.date
                di['content'] = item.content
                di['demands'] = item.demands
                di['abstract'] = item.abstract
                di['feedback'] = ""
                if item.feedback is not None:
                    di['feedback'] = item.feedback
                stu = Student.query.filter_by(id=item.stu_id).first()
                if stu is None:
                    continue
                di['department'] = stu.department
                di['major'] = stu.major
                di['grade'] = stu.grade
                di['contact'] = stu.contact
                di['sex']=stu.sex
                di['cancel'] = True                    
                di['readandwrite'] = True
                if item.status!=CONSULT_TODO:
                    di['readandwrite'] = False
                    di['cancel'] = False
                if item.pb_id==INVALID_TAG:
                    di['cancel'] = False
                    di['date'] = "咨询师发起咨询"
                li_.append(di)
                
    return render_template('teacher_query.html',form = LoginForm(), lists=li_, cont=tch.contact,name=tch.name, tcid=tch.seq)

@app.route('/get_teacher_cancel_page', methods=['GET', 'POST'])
@login_required    
def get_teacher_cancel_page():
    contact = request.args.get('cont')
    yyseq = request.args.get('yyseq')
    
    return render_template('teacher_cancel.html',fpcont=contact,yyseq=yyseq)

@app.route('/teacher_cancel', methods=['GET', 'POST'])
@login_required
def teacher_cancel():
    contact = request.form.get('contact')
    recid = request.form.get('yyseq')
    verycode = request.form.get('verycode')
    fd = request.form.get('feedback')
    
    msg = ""
    ret = False
    
    try:
        tch = current_user
        if contact != tch.contact:
            msg = "您获取验证码的手机和注册手机号不一致，无权取消"
            return jsonify(msg=msg,ret=ret)
        
        if recid is None:
            msg = "您取消的预约记录编号非法，请重新确认"
            return jsonify(msg=msg,ret=ret)
            
        if verycode != session.get('very_code'):
            msg = "您输入的验证码不正确，请重新输入"
            return jsonify(msg=msg,ret=ret)
    except:
        msg = "验证过程出现错误，请重新验证"
        return jsonify(msg=msg,ret=ret)
        
    hisrec = HistoryRecord.query.filter_by(id=recid).first()
    if hisrec is not None:
        consulttime = DateAndTimeHandler().YYTimeToDateTime(hisrec.date)
        if consulttime is None:
            msg = "预约记录的咨询时间格式有误，无法取消，请联系管理员处理！"
            return jsonify(msg=msg,ret=ret)
        else:
            curtime = datetime.datetime.now()
            if (consulttime-curtime).days == 0 and (consulttime-curtime).seconds<7200:
                msg = "咨询开始前2小时内不予取消！"
                return jsonify(msg=msg,ret=ret)
            elif (consulttime-curtime).days < 0:
                msg = "咨询开始后不能取消！"
                return jsonify(msg=msg,ret=ret)
    
        hisrec.status = CONSULT_CANCEL
        hisrec.t_can_res = fd
        hisrec.s_can_res = fd
        hisrec.timestamp = datetime.datetime.now()
        db.session.add(hisrec)
        
        sch = Schedule.query.filter_by(id=hisrec.pb_id).first()
        if sch is not None and sch.isboot:
            sch.isboot = 0
            sch.currecid = INVALID_TAG
            db.session.add(sch)
        elif sch is None:
            msg = "预约记录所对应的排班信息不存在,可能是由咨询师发起的咨询,填写反馈即可"
            return jsonify(msg=msg,ret=ret)
        else:
            msg = "预约记录所对应的排班显示未被预约"
            return jsonify(msg=msg,ret=ret)
            
        stu = Student.query.filter_by(id=hisrec.stu_id).first()
        if stu is not None:
            stu.currecid = INVALID_TAG
            db.session.add(stu)
        else:
            msg = "预约记录所对应的学生不存在"
            return jsonify(msg=msg, ret=ret)
            
        db.session.commit()
        msg = "成功取消咨询预约，以已短信方式告知" + stu.name
        
        ret = True
        
        # 给学生发送预约被取消的通知短信
        try:
            yytime = DateAndTimeHandler().GetYYDateAndTime(sch.wkd_id)
            yytime = yytime + " " + sch.stm_id
            sendmsgtostu = sch.tch_name+"已取消"+yytime+"的咨询预约"
            tostu = SmsHandler.SendSmsMsg(stu.contact, sendmsgtostu, 395042)
        except:
            msg = "已成功取消咨询，但向对方发送短信失败"
    else:
        msg = "您所要取消的预约记录不存在"

    return jsonify(msg=msg, ret=ret)

# 6.咨询老师增加学生的反馈信息
@app.route('/add_stu_feedback', methods=['GET', 'POST'])
@login_required
def add_stu_feedback():
    fd = request.form.get('feedback')
    recid = request.form.get('recid')
    
    msg = ""
    ret = False

    hisrec = HistoryRecord.query.filter_by(id=recid).first()
    if hisrec is not None:
        consulttime = DateAndTimeHandler().YYTimeToDateTime(hisrec.date)
        if consulttime is None:
            if hisrec.pb_id==INVALID_TAG:
                pass
            else:
                msg = "预约记录的咨询时间格式有误，无法增加反馈，请联系管理员处理！"
                return jsonify(msg=msg,ret=ret)
        else:
            curtime = datetime.datetime.now()
            if consulttime>curtime:
                msg = "咨询开始前禁止添加咨询反馈！"
                return jsonify(msg=msg,ret=ret)
                
        hisrec.status = CONSULT_COMPLETE
        hisrec.feedback = fd
        hisrec.timestamp = datetime.datetime.now()
        db.session.add(hisrec)
        
        sch = Schedule.query.filter_by(id=hisrec.pb_id).first()
        if sch is not None and sch.isboot:
            sch.isboot = 0
            sch.currecid = INVALID_TAG
            db.session.add(sch)
        elif sch is None:
            if hisrec.pb_id!=INVALID_TAG:
                msg = "预约记录所对应的排班不存在"
                return jsonify(msg=msg,ret=ret)
        else:
            msg = "预约记录所对应的排班显示未被预约"
            return jsonify(msg=msg,ret=ret)
            
        stu = Student.query.filter_by(id=hisrec.stu_id).first()
        if stu is not None:
            stu.currecid = INVALID_TAG
            db.session.add(stu)
        else:
            msg = "预约记录所对应的学生不存在"
            return jsonify(msg=msg, ret=ret)
            
        ret = True
        db.session.commit()
        msg = "成功添加反馈信息，此次咨询已完成！"
    else:
        msg = "预约记录不存在，请确认后再提交反馈"
        ret = False

    return jsonify(msg=msg,ret=ret)

#管理员修改学生的状态
@app.route('/set_stu_status', methods=['GET', 'POST'])
@login_required
def set_stu_status(): 
    cont = request.form.get('contact')
    st = request.form.get('status')
    
    stu = Student.query.filter_by(contact=cont).first()
    if stu is not None:
        st_int = stu.status
        try:
            if int(st) > STU_BLACK or int(st) < STU_INIT:
                pass
            else:
                st_int = int(st)
        except:
            pass
        stu.status = int(st)
            
        db.session.add(stu)
        db.session.commit()
        
    return jsonify(status="0", msg="OK")     
    
# 全部清除已有预约记录，NOTE:暂未提供客户访问接口
@app.route("/clear_history_records", methods=['GET', 'POST'])
@login_required
def clear_history_records():
    if current_user is not None:
        hisrec = HistoryRecord.query.all()
        for item in hisrec:
            stu = Student.query.filter_by(id=item.stu_id).first()
            if stu is not None and stu.currecid!=INVALID_TAG:
                stu.currecid = INVALID_TAG
                db.session.add(stu)
            tch = Teacher.query.filter_by(seq=item.tch_id).first()
            if tch is not None:
                tch.isboot = 0
                tch.currecid = INVALID_TAG
                db.session.add(tch)
            db.session.delete(item)
            
        db.session.commit()
        return render_template('admin.html',form = LoginForm(), name=current_user.name)
    
    cooname = request.cookies.get('tchname')
    coopw = request.cookies.get('tchpw')
    coorm = request.cookies.get('tchrm')
    if cooname is None:
        cooname,coopw,coorm = "","",""
    return render_template('admin_teacher.html', form=LoginForm(),coct=cooname,copw=coopw,corm=coorm)
    
##! 导入导出文件操作
@app.route("/import", methods=['GET', 'POST'])
@login_required
def doimport():
    if request.method == 'POST':
        try:
            def teacher_init_func(row):
                tch = Teacher(row['name'],row['contact'],row['email'],row['password'],row['date'],row['href'],row['position'],row['isboot'],row['role'],datetime.datetime.now())
                return tch

            def student_init_func(row):
                stu = Student(row['name'],row['sex'],row['contact'],row['password'],row['stuID'],row['email'],row['department'], \
                              row['major'],row['grade'],row['canceltimes'],row['status'],datetime.datetime.now())
                return stu
                
            def hisrec_init_func(row):
                his_rec = HistoryRecord(row['tch_id'],row['tch_name'],row['stu_id'],row['stu_name'],row['date'],row['status'],row['content'],row['timestamp'])
                
                return his_rec
            
            request.save_book_to_database(field_name='file', session=db.session,
                tables=[Teacher,Student,HistoryRecord], initializers=[teacher_init_func,student_init_func,hisrec_init_func])
        except:
            pass
        return render_template('index.html',form=Form())
    
    return render_template('import.html',form=Form(),imadd="import")

def allowed_file(filename):
    return '.' in filename and \
        filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS   

@app.route("/import_stus_from_tch", methods=['GET', 'POST'])
@login_required
def import_stus_from_tch():
    if request.method == 'POST':
        from pyexcel_xls import get_data
        from werkzeug import secure_filename
        try:
            file = request.files['file']
            if file and allowed_file(file.filename):
                filename = secure_filename(file.filename)
                ccLog.info("上传的文件名称："+filename)
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
                data = get_data(os.path.join(app.config['UPLOAD_FOLDER'], filename))
                ccLog.info("获取上传咨询师发起咨询文件中数据")
                
                sh_no = 0
                for sheet_n in data.keys():
                    sh_no = sh_no + 1
                    
                    sh_data = data[sheet_n]
                    
                    idx_row = sh_data[1]
                    noseq = idx_row.index(u'序号')
                    nodepartment = idx_row.index(u'院系')
                    nomajor = idx_row.index(u'专业')
                    noname = idx_row.index(u'姓名')
                    nograde = idx_row.index(u'年级')
                    nophoneno = idx_row.index(u'手机号')
                    nopwill = idx_row.index(u'求职意向')
                    norequest = idx_row.index(u'培训需求')
                    nonote = idx_row.index(u'备注')
                    notch = idx_row.index(u'咨询师姓名')
                    
                    idx = 2
                    while idx<len(sh_data):
                        try:
                            curls = sh_data[idx]
                            
                            phone_no = str(curls[nophoneno]).split(".")[0]
                            
                            tch = Teacher.query.filter_by(name=curls[notch]).first()
                            if tch is None:
                                ccLog.error("处理文件出错:咨询师"+str(curls[notch])+"不存在")
                                idx = idx + 1
                                continue
                            stu = Student.query.filter_by(contact=phone_no).first()
                            if stu is None:
                                stu = Student(curls[noname], "None", phone_no, "123456", "ID:"+phone_no, "email:"+phone_no, "", "", "", 0, STU_ARRANGED, datetime.datetime.now())
                                if stu is None:
                                    idx = idx + 1
                                    continue
                                else:
                                    db.session.add(stu)
                                    db.session.commit()
                                    
                            his_rec = HistoryRecord(tch.seq, tch.name, stu.id, stu.name, INVALID_TAG, "", CONSULT_TODO, curls[nopwill], curls[norequest], datetime.datetime.now())
                            if his_rec is not None:
                                db.session.add(his_rec)
                                db.session.commit()
                                
                                his_rec.abstract = curls[nonote]
                                
                                db.session.add(his_rec)
                                db.session.commit()
                            else:
                                ccLog.error("新建预约记录失败")
                            
                            #print(curls[notch])
                            
                            idx = idx + 1
                        except Exception as e:
                            ccLog.error("处理文件出错："+str(e))
                            flash("处理文件出错："+str(e))
                            idx = idx + 1
                            pass
                        
                    
                    if sh_no==1:
                        break
                
        except Exception as e:
            ccLog.error("导入文件格式可能有误，错误："+str(e))
            return render_template('msg_handler.html',msg="导入文件格式可能有误，第二行需要包含如下列：序号、院系、专业、姓名、年级、手机号、求职意向、培训需求、备注、咨询师姓名。错误："+str(e))
        
        return render_template('admin.html',form = LoginForm(), name=current_user.name)
    
    return render_template('import.html',form=Form(),imadd="import_stus_from_tch")

@app.route("/import_tch", methods=['GET', 'POST'])
@login_required
def import_tch():
    if request.method == 'POST':        
        from pyexcel_xls import get_data
        from werkzeug import secure_filename
        try:
            file = request.files['file']
            if file and allowed_file(file.filename):
                filename = secure_filename(file.filename)
                ccLog.info("上传的文件名称："+filename)
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
                data = get_data(os.path.join(app.config['UPLOAD_FOLDER'], filename))
                ccLog.info("获取上传文件中数据")

                ltop = data[0]
                noseq = ltop.index(u'序号')
                noname = ltop.index(u'姓名')
                nocont = ltop.index(u'电话')
                noemail = ltop.index(u'邮箱')
                nopw = ltop.index(u'密码')
                nopbid = ltop.index(u'排班ID')
                notime = ltop.index(u'咨询时段')
                nohref = ltop.index(u'个人链接')
                nopos = ltop.index(u'校区ID')
                nointro = ltop.index(u'个人简介')
                
                idx = 1
                hasnew = False
                while idx < len(data):
                    curls = data[idx]
                    cont = curls[nocont]
                    
                    try:
                        tch = Teacher.query.filter_by(contact=cont).first()
                        if tch is not None:
                            pass
                        else:
                            if curls[noseq] is None or curls[noseq]=="":
                                pass
                                
                            tch = Teacher(curls[noseq],curls[noname],curls[nocont],curls[noemail],curls[nopw],curls[nohref],curls[nopos],curls[nointro],None,ROLE_TEACHER,datetime.datetime.now())
                            db.session.add(tch)
                            db.session.commit()
                            hasnew = True
                    except Exception as e:
                        ccLog.info("查询或创建新的咨询师失败")
                        ccLog.info("错误："+str(e))
                        pass
                    
                    try:
                        schd = Schedule(tch.seq,tch.name,tch.contact,curls[nopbid],curls[notime],curls[nopos],datetime.datetime.now())
                        db.session.add(schd)
                        hasnew = True
                    except:
                        pass
                        
                    """
                    try:
                        tch = Teacher(curls[noname],curls[nocont],curls[noemail],curls[nopw],curls[nohref],curls[nopos])
                        db.session.add(tch)
                        hasnew = True
                    except:
                        pass
                    """
                    idx = idx+1
                
                if hasnew:
                    db.session.commit()
            else:
                ccLog.error("导入文件为空")
        except Exception as e:
            ccLog.error("导入文件格式可能有误，错误："+str(e))
            return render_template('msg_handler.html',msg="导入文件格式可能有误，需要包含如下列：序号、姓名、电话、邮箱、密码、排班ID、咨询时段、个人链接、校区ID、个人简介。错误："+str(e))
        
        return render_template('admin.html',form = LoginForm(), name=current_user.name)
    
    return render_template('import.html',form=Form(),imadd="import_tch")
    
@app.route("/import_stu", methods=['GET', 'POST'])
@login_required
def import_stu():
    if request.method == 'POST':
        try:
            def student_init_func(row):
                stu = Student(row['name'],row['sex'],row['contact'],row['password'],row['stuID'],row['email'],row['department'], \
                              row['major'],row['grade'],row['canceltimes'],row['status'],datetime.datetime.now())
                return stu
            
            request.save_book_to_database(field_name='file', session=db.session,
                tables=[Student], initializers=[student_init_func])
        except Exception as e:
            pass
        return render_template('index.html',form=Form())
    
    return render_template('import.html',form=Form(),imadd="import_stu")

@app.route("/import_yy", methods=['GET', 'POST'])
@login_required
def import_yy():
    if request.method == 'POST':
        try:               
            def hisrec_init_func(row):
                his_rec = HistoryRecord(row['tch_id'],row['tch_name'],row['stu_id'],row['stu_name'],row['date'],row['status'],row['content'],row['timestamp'])
                
                return his_rec
            
            request.save_book_to_database(field_name='file', session=db.session,
                tables=[HistoryRecord], initializers=[hisrec_init_func])
        except:
            pass
        return render_template('index.html',form=Form())
    
    return render_template('import.html',form=Form(),imadd="import_yy")

@app.route("/export", methods=['GET','POST'])
@login_required
def export():
    import pyexcel as pe
    import pyexcel_xls
    from collections import OrderedDict
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            #return excel.make_response_from_tables(db.session, [Teacher, Student, HistoryRecord], "xls")
            
            stulist = []
            stulist.append([u'序号',u'姓名',u'性别',u'电话',u'密码',u'学号',u'学院',u'专业',u'年级',u'状态',u'取消预约次数',u'预约记录序号'])
            stus = Student.query.all()
            if stus is not None:
                for item in stus:
                    l = []
                    l.append(item.seq)
                    l.append(item.name)
                    l.append(item.sex)
                    l.append(item.contact)
                    l.append(item.password)
                    l.append(item.stuID)
                    l.append(item.department)
                    l.append(item.major)
                    l.append(item.grade)
                    l.append(NjuUtility.GetStuStatus(item.status))
                    l.append(item.canceltimes)
                    l.append(item.currecid)
                    stulist.append(l)
            
            yylist = []
            yylist.append([u'序号',u'学生',u'教师',u'咨询内容',u'咨询时间',u'需求',u'反馈',u'预约时间',u'状态'])
            recs = HistoryRecord.query.all()
            if recs is not None:
                for item in recs:
                    l = []
                    l.append(item.id)
                    l.append(item.stu_name)
                    l.append(item.tch_name)
                    l.append(item.content)
                    l.append(item.date)
                    l.append(item.demands)
                    l.append(item.feedback)
                    l.append(item.timestamp)
                    l.append(NjuUtility.GetYYStatus(item.status))
                    yylist.append(l)
                    
            sheet = pe.Sheet(stulist)
            sheet1 = pe.Sheet(yylist)
            sheets = OrderedDict()
            sheets['student_info'] = sheet
            sheets['booting_records'] = sheet1
            book = pe.Book(sheets)
            
            return excel.make_response(book, "xls", 200, 'student_and_booting_records')
                    
    return render_template('500.html'), 500
    
@app.route("/export_tch", methods=['GET','POST'])
@login_required
def export_tch():
    from collections import OrderedDict
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            query_sets = Teacher.query.filter_by(role=ROLE_TEACHER).all()
            ep = OrderedDict()
            name = []
            seq = []
            cont = []
            date = []
            dateid = []
            time_ = []
            email = []
            href = []
            isboot = []
            pasw = []
            pos = []
            posid = []
            intro = []
                        
            for item in query_sets:
                schs = Schedule.query.filter_by(tch_id=item.seq).all()
                index_i = 0
                for elem in schs:
                    seq.append(item.seq)
                    name.append(item.name)
                    cont.append(item.contact)
                    date.append(DateAndTimeHandler().GetTchDateAndTime(elem.wkd_id))
                    dateid.append(elem.wkd_id)
                    time_.append(elem.stm_id)
                    email.append(item.email)
                    href.append(item.href)
                    if elem.isboot:
                        isboot.append(u'是')
                    else:
                        isboot.append(u'否')
                    pasw.append(item.password)
                    posid.append(elem.campus)
                    if index_i == 0:
                        intro.append(item.intro)
                    else:
                        intro.append(u'')
                    index_i = index_i+1
                    if elem.campus==DISTRICT_GULOU:
                        pos.append(u"鼓楼校区")
                    elif elem.campus==DISTRICT_XIANLIN:
                        pos.append(u"仙林校区")
                    else:
                        pos.append(u"")
            
            hasele = len(query_sets)>0

            ep[u"序号"] = seq
            ep[u"姓名"] = name
            ep[u"电话"] = cont
            if hasele:
                ep[u"排班"] = date
                ep[u"预约"] = isboot
            ep[u"邮箱"] = email
            ep[u"个人链接"] = href
            
            ep[u"密码"] = pasw
            if hasele:
                ep[u"所在校区"] = pos
            
            ep[u'排班ID'] = dateid
            ep[u'咨询时段'] = time_
            ep[u'校区ID'] = posid
            ep[u'个人简介'] = intro
            
            if not hasele:
                ep[u'排班ID'].append(u'排班id为数字:1到5代表周一到周五，如需输入，请删除本段文字并填入数字代码')
                ep[u'校区ID'].append(u'校区id栏，1代表鼓楼校区，2代表仙林校区，如需输入，请删除本段文字并填入数字代码')
                return excel.make_response_from_dict(ep, "xls", 200, "teacher_info_template")
                
            return excel.make_response_from_dict(ep, "xls", 200, "teacher_info")
    return render_template('500.html'), 500
   
@app.route("/export_template", methods=['GET','POST'])
@login_required
def export_template():
    from collections import OrderedDict
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            query_sets = Teacher.query.filter_by(role=ROLE_TEACHER).all()
            ep = OrderedDict()
            name = []
            id = []
            cont = []
            dateid = []
            email = []
            href = []
            pasw = []
            posid = []
                        
            ep[u"序号"] = id
            ep[u"姓名"] = name
            ep[u"电话"] = cont
            ep[u"邮箱"] = email
            ep[u"个人链接"] = href
            
            ep[u"密码"] = pasw
            
            ep[u'排班ID'] = dateid
            ep[u'校区ID'] = posid
            
            ep[u'排班ID'].append(u'排布id格式如1:1，冒号为英文输入格式下的，冒号:左边1到5代表周一到周五，右边1到4代表咨询时段，如需输入，请删除本段文字并填入数字代码，注意如1:1在单元格中向左对齐！谨防1:1被excel自动格式化为01:01:00！')
            ep[u'校区ID'].append(u'校区id，1代表鼓楼校区，2代表仙林校区，如需输入，请删除本段文字并填入数字代码')
            return excel.make_response_from_dict(ep, "xls", 200, "teacher_info_template")
                
    return render_template('500.html'), 500
        
@app.route("/export_stu", methods=['GET','POST'])
@login_required
def export_stu():
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            return excel.make_response_from_tables(db.session, [Student], "xls")
    return render_template('500.html'), 500
    
@app.route("/export_records", methods=['GET','POST'])
@login_required
def export_records():
    yearnum = request.args.get('yearnum')
    weeknum = request.args.get('weeknum')
    
    yrno = 0
    wkno = 0
    try:
        yrno = int(yearnum)
        wkno = int(weeknum)
    except:
        flash("您输入的年份和周数不正确，请重新输入数字")
        return render_template("admin_records.html", form=LoginForm())
        
    if wkno < 1 or wkno>12: #52:
        flash("月数只能为1-12中的整数")
        return render_template("admin_records.html", form=LoginForm())
        
    if yrno != 2016 and yrno != 2017 and yrno != 2018:
        flash("请输入正确的年份，如：2016")
        return render_template("admin_records.html", form=LoginForm())
        
    from collections import OrderedDict
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            ep = OrderedDict()

            id = []
            
            tchname = []
            tchid = []
            stuname = []
            stuid = []
            
            date = []
            status = []
            content = []
            demands = []
            feedback = []
            s_can_res = []
            abstract = []
            newdate = []
            
            query_sets = HistoryRecord.query.all()
            for item in query_sets:
                try:
                    dateitems = item.date.split('-')
                    #ddnum = datetime.date(int(dateitems[0]), int(dateitems[1]), int(dateitems[2])).isocalendar()

                    if int(dateitems[0]) != yrno or wkno!=int(dateitems[1]):
                        continue
                except Exception as e:
                    #print(str(e))
                    continue
                    
                id.append(item.id)
                tch = Teacher.query.filter_by(seq=item.tch_id).first()
                if tch is not None:
                    tchname.append(tch.name)
                    tchid.append(tch.seq)
                else:
                    tchname.append("")
                    tchid.append("")
                    
                stu = Student.query.filter_by(id=item.stu_id).first()
                if stu is not None:
                    stuname.append(stu.name)
                    stuid.append(stu.id)
                else:
                    stuname.append("")
                    stuid.append("")
                    
                date.append(item.date)
                status.append(NjuUtility.GetYYStatus(item.status))
                content.append(item.content)
                demands.append(item.demands)
                feedback.append(item.feedback)
                s_can_res.append(item.s_can_res)
                abstract.append(item.abstract)
                newdate.append(item.timestamp.strftime("%Y-%m-%d %H:%M"))
            
            ep[u"序号"] = id
            ep[u"咨询师"] = tchname
            ep[u"咨询师ID"] = tchid
            ep[u"学生"] = stuname
            ep[u"学生ID"] = stuid    
            
            ep[u"咨询日期"] = date
            ep[u'咨询状态'] = status
            ep[u'咨询内容'] = content
            ep[u'需求'] = demands
            ep[u'咨询反馈'] = feedback
            ep[u'取消理由'] = s_can_res
            ep[u'摘要'] = abstract
            ep[u'最后修改时间'] = newdate
            
            return excel.make_response_from_dict(ep, "xls", 200, "caree_counselling_records_"+str(yrno)+"_"+str(wkno))
    return render_template('500.html'), 500
    
@app.route("/export_all_records", methods=['GET','POST'])
@login_required
def export_all_records():
    from collections import OrderedDict
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            ep = OrderedDict()

            id = []
            
            tchname = []
            tchid = []
            stuname = []
            stuid = []
            
            date = []
            status = []
            content = []
            demands = []
            feedback = []
            s_can_res = [] #所有的取消理由全部记录在s_can_res
            abstract = []
            newdate = []
            
            query_sets = HistoryRecord.query.all()
            for item in query_sets:                    
                id.append(item.id)
                tch = Teacher.query.filter_by(seq=item.tch_id).first()
                if tch is not None:
                    tchname.append(tch.name)
                    tchid.append(tch.seq)
                else:
                    tchname.append("")
                    tchid.append("")
                    
                stu = Student.query.filter_by(id=item.stu_id).first()
                if stu is not None:
                    stuname.append(stu.name)
                    stuid.append(stu.id)
                else:
                    stuname.append("")
                    stuid.append("")
                    
                date.append(item.date)
                status.append(NjuUtility.GetYYStatus(item.status))
                content.append(item.content)
                demands.append(item.demands)
                feedback.append(item.feedback)
                s_can_res.append(item.s_can_res)
                abstract.append(item.abstract)
                newdate.append(item.timestamp.strftime("%Y-%m-%d %H:%M"))
            
            ep[u"序号"] = id
            ep[u"咨询师"] = tchname
            ep[u"咨询师ID"] = tchid
            ep[u"学生"] = stuname
            ep[u"学生ID"] = stuid    
            
            ep[u"咨询日期"] = date
            ep[u'咨询状态'] = status
            ep[u'咨询内容'] = content
            ep[u'需求'] = demands
            ep[u'咨询反馈'] = feedback
            ep[u'取消理由'] = s_can_res
            ep[u'摘要'] = abstract
            ep[u'最后修改时间'] = newdate
            
            return excel.make_response_from_dict(ep, "xls", 200, "caree_counselling_records")
    return render_template('500.html'), 500

@app.route('/ckupload/', methods=['GET','POST'])
@login_required
def ckupload():
    error = ''
    url = ''

    callback = request.args.get("CKEditorFuncNum")
    if request.method == 'POST' and 'upload' in request.files:
        fileobj = request.files['upload']
        fname, fext = os.path.splitext(fileobj.filename)
        rnd_name = '%s%s' % (gen_rnd_filename(), fext)
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], '..', 'app', 'static', 'article', rnd_name)
        # 检查路径是否存在，不存在则创建
        dirname = os.path.dirname(filepath)
        if not os.path.exists(dirname):
            try:
                os.makedirs(dirname)
            except:
                error = 'ERROR_CREATE_DIR'
        elif not os.access(dirname, os.W_OK):
            error = 'ERROR_DIR_NOT_WRITEABLE'
        if not error:
            fileobj.save(filepath)
            url = url_for('static', filename='%s/%s' % ('article', rnd_name))
    else:
        error = 'post error'
    res = """

    <script type="text/javascript">
     window.parent.CKEDITOR.tools.callFunction(%s, '%s', '%s');
    </script>

    """ % (callback, url, error)
    response = make_response(res)
    response.headers["Content-Type"] = "text/html"
    return response

@app.route('/fileupload', methods=['GET','POST'])
@login_required
def fileupload():
    error = ''
    url = ''

    if request.method == 'POST' and 'file' in request.files:
        fileobj = request.files['file']
        fname, fext = os.path.splitext(fileobj.filename)
        rnd_name = '%s%s' % (gen_rnd_filename(), fext)
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], '..', 'app', 'static', 'article', rnd_name)
        # 检查路径是否存在，不存在则创建
        dirname = os.path.dirname(filepath)
        if not os.path.exists(dirname):
            try:
                os.makedirs(dirname)
            except:
                error = 'ERROR_CREATE_DIR'
        elif not os.access(dirname, os.W_OK):
            error = 'ERROR_DIR_NOT_WRITEABLE'
        if not error:
            fileobj.save(filepath)
            url = url_for('static', filename='%s/%s' % ('article', rnd_name))
    else:
        error = 'post error'
    return jsonify(url=url,error=error)

##! 错误处理
@app.route('/msg_handler<msg>',  methods=['GET', 'POST'])
def msg_handler(msg):
    return render_template('msg_handler.html',msg=msg)
    
@app.errorhandler(400)
def crsf_missing_or_error(error):
    flash("长时间未操作，请重新进入")
    return render_template('index.html',form=Form()), 400
    
@app.errorhandler(404)
def not_found_error(error):
    return render_template('404.html'), 404

@app.errorhandler(500)
def internal_error(error):
    db.session.rollback()
    return render_template('500.html'), 500



